package com.niu.crm.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.niu.crm.service.BaseService;
import com.niu.crm.service.ExamService;
import com.niu.crm.service.StudentService;
import com.niu.crm.core.error.BizException;
import com.niu.crm.core.error.GlobalErrorCode;
import com.niu.crm.dao.mapper.MaxidMapper;
import com.niu.crm.dao.mapper.StudentParentMapper;
import com.niu.crm.dao.mapper.StudentScoreMapper;
import com.niu.crm.dao.mapper.StudentMapper;
import com.niu.crm.form.StudentExportForm;
import com.niu.crm.form.StudentSearchForm;
import com.niu.crm.model.Exam;
import com.niu.crm.model.Maxid;
import com.niu.crm.model.StuParent;
import com.niu.crm.model.StuScore;
import com.niu.crm.model.Student;
import com.niu.crm.model.User;

@Service
public class StudentServiceImpl extends BaseService implements StudentService{
    @Autowired
    private StudentMapper  studentMapper;
    @Autowired
    private StudentParentMapper  stuParentMapper;
    @Autowired
    private StudentScoreMapper  stuScoreMapper;
    @Autowired
    private MaxidMapper  maxidMapper;
    @Autowired
    private ExamService examService;

    /**
     * 学生唯一性字段 (NAME/NO), NO必须唯一
     */
    @Override
    public String getUniqueFld(){
    	return "NAME";
    }

    @Override
	public Student load(Long id){
		Student stu = studentMapper.selectByPrimaryKey(id);
		if(stu == null)
			return stu;
		
		List<StuParent> lsParent = stuParentMapper.selectByStuId(id);
		if(lsParent == null)
			lsParent = new ArrayList<>();
		
		stu.setStuParents(lsParent);
		
		return stu;
	}

	@Transactional
	private int save(User user, Student student){    	
		//student.setStuNo( createStuNo() );

    	Long stuId = student.getId();
    	student.setCreatorId(user.getId());    	
    	if(stuId == null){
    		studentMapper.insert(student);
    		stuId = student.getId();

        	StuScore stuScore = new StuScore();
        	stuScore.setStuId(stuId);
        	stuScoreMapper.insert(stuScore);
    	}else{
    		stuParentMapper.deleteByStuId(stuId);
    	}
    	
    	List<StuParent> ls = student.getStuParents();
    	for(int i=0; ls !=null && i < ls.size(); i++){
    		StuParent p = ls.get(i);
    		
    		if(StringUtils.isEmpty(p.getRelation()) )
    			p.setRelation(null);
    		if(StringUtils.isEmpty(p.getName()) )
    			p.setName(null);
    		if(StringUtils.isEmpty(p.getMobile()) )
    			p.setMobile(null);
    		
    		if(p.getName() == null && p.getMobile() == null)
    			continue;
    		
    		p.setStuId(stuId);
    		p.setCreatorId(user.getId());
    		stuParentMapper.insert(p);
    	}
    	
    	return 1;
	}
	
	@Transactional
    @Override
	public int insert(User user, Student student){    	
		//student.setStuNo( createStuNo() );
    	student.setCreatorId(user.getId());    	
    	int count = studentMapper.insert(student);
    	
    	Long stuId = student.getId();
    	StuScore stuScore = new StuScore();
    	stuScore.setStuId(stuId);
    	stuScoreMapper.insert(stuScore);
    	
    	List<StuParent> ls = student.getStuParents();
    	for(int i=0; ls !=null && i < ls.size(); i++){
    		StuParent p = ls.get(i);
    		
    		if(StringUtils.isEmpty(p.getRelation()) )
    			p.setRelation(null);
    		if(StringUtils.isEmpty(p.getName()) )
    			p.setName(null);
    		if(StringUtils.isEmpty(p.getMobile()) )
    			p.setMobile(null);
    		
    		if(p.getName() == null && p.getMobile() == null)
    			continue;
    		
    		p.setStuId(stuId);
    		p.setCreatorId(user.getId());
    		stuParentMapper.insert(p);
    	}
    	
    	return count;
	}
    
    @Transactional
    @Override
	public int update(User user, Student student){
    	int count = studentMapper.update(student);
    	List<StuParent> ls = student.getStuParents();
    	
    	stuParentMapper.deleteByStuId(student.getId());
    	for(int i=0; ls !=null && i < ls.size(); i++){
    		StuParent p = ls.get(i);
    		p.setStuId(student.getId());
    		p.setCreatorId(user.getId());
    		
    		stuParentMapper.insert(p);  			
    	}
    	
    	return count;
	}
    
    @Override
    public int updateScore(Long stuId, Integer examNo, BigDecimal score){
    	String examFld = "exam_" + examNo;
    	StuScore stuScore = stuScoreMapper.selectByStuId(stuId);
    	if(stuScore == null){
    		stuScore = new StuScore();
    		stuScore.setStuId(stuId);
    		stuScoreMapper.insert(stuScore);
    	}
    	Integer count= stuScoreMapper.updateScore(stuScore.getId(), examFld, score);
    	return count == null?0:count.intValue();
    }
    
    @Transactional
    @Override
    public void delete(Long id){
    	stuScoreMapper.deleteByStuId(id);
    	stuParentMapper.deleteByStuId(id);
    	studentMapper.delete(id);
    }
    
    @Override
    public Workbook exportStudents(StudentExportForm form, Pageable pager){
		if(form.getScoreTemplate() !=null && form.getScoreTemplate() ){
			return exportScoreTemplate(form);
		}
    	
		
    	Workbook wb = new XSSFWorkbook();
    	Sheet  sheet = wb.createSheet();
		Row row = null;
		Cell cell = null;
		
		int cellnum=0, rownum = 0;

		List<Student> students = studentMapper.queryStudent(form, pager);
		List<Exam> exams = examService.loadAll();
		
		boolean expParent = true;
		
		CellStyle titleStyle=null, cellStyle, scoreStyle;

		titleStyle = wb.createCellStyle();
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		titleStyle.setBorderBottom(BorderStyle.THIN);
		titleStyle.setBorderRight(BorderStyle.THIN);
		
		cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.LEFT);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		
		scoreStyle = wb.createCellStyle();
		scoreStyle.setAlignment(HorizontalAlignment.RIGHT);
		scoreStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		scoreStyle.setBorderBottom(BorderStyle.THIN);
		scoreStyle.setBorderRight(BorderStyle.THIN);

		try {
			// 第一行
			row = sheet.createRow(rownum++);
			/********编号, 姓名, 学校, **********/ 
			
			cell = row.createCell(cellnum ++);
			cell.setCellValue("学号");
			cell.setCellStyle(titleStyle);
			row.createCell(cellnum ++).setCellValue("姓名");
			
			
			row.createCell(cellnum ++).setCellValue("性别");
			row.createCell(cellnum ++).setCellValue("学校");
				
			if(expParent) {
				row.createCell(cellnum++).setCellValue("联系方式");
				row.createCell(cellnum++);
				row.createCell(cellnum++);
				row.createCell(cellnum++);
				
				sheet.addMergedRegion(new CellRangeAddress(0, 0, cellnum -4, cellnum-1));
			}

			row.createCell(cellnum ++).setCellValue("年级");
			row.createCell(cellnum ++).setCellValue("时段");
			row.createCell(cellnum ++).setCellValue("缴费");
			
			//成绩
			for(Exam exam:exams){
				row.createCell(cellnum ++).setCellValue( exam.getName() );
			}
				
			row.createCell(cellnum ++).setCellValue("备注");
			
			for(cellnum=0; cellnum < row.getLastCellNum(); cellnum ++){
				row.getCell(cellnum).setCellStyle(titleStyle);
			}
			
			
			/***********************************/
			List<StuParent> stuParents = null;
			for(Student stu:students){
				StuScore stuScore = stu.getStuScore();
				if(expParent){
					stuParents = stuParentMapper.selectByStuId(stu.getId());
				}else{
					stuParents = new ArrayList<>();
				}
				
				for (int i = 0; i == 0 || i < stuParents.size(); i++) {
					StuParent stuParent = null;
					if(stuParents.size() > i)
						stuParent = stuParents.get(i);
					

					cellnum =0;
					row = sheet.createRow(rownum++);

					cell = row.createCell(cellnum++);
					cell.setCellStyle(cellStyle);
					if (i == 0)
						cell.setCellValue(stu.getStuNo());
					cell = row.createCell(cellnum++);
					cell.setCellStyle(cellStyle);
					if (i == 0)
						cell.setCellValue(stu.getStuName());

					cell = row.createCell(cellnum++);
					cell.setCellStyle(cellStyle);
					if (i == 0)
						cell.setCellValue(stu.getGender());
					cell = row.createCell(cellnum++);
					cell.setCellStyle(cellStyle);
					if (i == 0)
						cell.setCellValue(stu.getSchool());

					if (expParent ) {
						cell = row.createCell(cellnum++);
						cell.setCellStyle(cellStyle);
						if(stuParent !=null)
							cell.setCellValue(stuParent.getRelation());
						cell = row.createCell(cellnum++);
						cell.setCellStyle(cellStyle);
						if(stuParent !=null)
							cell.setCellValue(stuParent.getName());
						cell = row.createCell(cellnum++);
						cell.setCellStyle(cellStyle);
						if(stuParent !=null)
							cell.setCellValue(stuParent.getDanwei());
						cell= row.createCell(cellnum++);
						cell.setCellStyle(cellStyle);
						if(stuParent !=null)
							cell.setCellValue(stuParent.getMobile());
					}


					cell = row.createCell(cellnum++);
					cell.setCellStyle(cellStyle);
					if (i == 0)
						cell.setCellValue(stu.getGrade());
					

					cell = row.createCell(cellnum++);
					cell.setCellStyle(cellStyle);
					if (i == 0)
						cell.setCellValue(stu.getTimeName());
					
					cell = row.createCell(cellnum++);
					cell.setCellStyle(cellStyle);
					if (i == 0)
						cell.setCellValue(stu.getFee());
					for (Exam exam : exams) {
						cell = row.createCell(cellnum++);
						cell.setCellStyle(scoreStyle);
						if (i == 0 && stuScore !=null){
							BigDecimal score = stuScore.getScore(exam.getNo());
							
							if (score != null) {
								cell.setCellValue(score.doubleValue());
								
							}
						}
					}

					cell = row.createCell(cellnum++);
					cell.setCellStyle(cellStyle);
					if (i == 0)
						cell.setCellValue(stu.getMemo());
				}
				if(stuParents.size() >1){
					CellRangeAddress range = null;
					int row1 = row.getRowNum() - stuParents.size() +1;
					int row2 = row.getRowNum();
					
					for(cellnum=0; cellnum <4; cellnum ++){
						range = new CellRangeAddress(row1, row2, cellnum, cellnum);
						sheet.addMergedRegion(range);
					}
					for(cellnum=8; cellnum < row.getLastCellNum(); cellnum ++){
						range = new CellRangeAddress(row1, row2, cellnum, cellnum);
						sheet.addMergedRegion(range);
					}
				}
			}

			// 设置列宽
			sheet.setDefaultColumnWidth((short) 8);
			sheet.setColumnWidth((short) 0, (short) (256 * 12));
			sheet.setColumnWidth((short) 1, (short) (256 * 12));
			//性别
			sheet.setColumnWidth((short) 2, (short) (256 * 6));
			//学校
			sheet.setColumnWidth((short) 3, (short) (256 * 17));
			//年级
			sheet.setColumnWidth((short) 4, (short) (256 * 8));
			//姓名
			sheet.setColumnWidth((short) 6, (short) (256 * 8));
			//单位
			sheet.setColumnWidth(7, (short) (256 * 17));
			//联系电话
			sheet.setColumnWidth(8, 256 * 13);
			
			sheet.setColumnWidth(row.getLastCellNum() -1, 256 * 25);

		} catch (Exception e) {
			getLogger().error("", e);
			throw e;
		} finally {
		}
		return wb;
    }
    
    private Workbook exportScoreTemplate(StudentExportForm form){
    	Workbook wb = new XSSFWorkbook();
    	Sheet  sheet = wb.createSheet();
		Row row = null;
		Cell cell = null;
		
		int cellnum=0, rownum = 0;

		List<Student> students = studentMapper.queryStudent(form, null);
		
		try {
			// 第一行
			row = sheet.createRow(rownum++);
			/********学号, 姓名, 成绩**********/ 
			
			cell = row.createCell(cellnum ++);
			cell.setCellValue("学号");
			row.createCell(cellnum ++).setCellValue("姓名");
			row.createCell(cellnum ++).setCellValue("成绩");
		
			for(Student stu:students){
				cellnum =0;
				
				row = sheet.createRow(rownum++);

				cell = row.createCell(cellnum++);
				cell.setCellValue(stu.getStuNo());
					
				cell = row.createCell(cellnum++);
				cell.setCellValue(stu.getStuName());

				cell = row.createCell(cellnum++);
			}

			// 设置列宽
			sheet.setDefaultColumnWidth((short) 17);
			sheet.setColumnWidth((short) 0, (short) (256 * 21));
			sheet.setColumnWidth((short) 1, (short) (256 * 21));

		} catch (Exception e) {
			getLogger().error("", e);
			throw e;
		} finally {
		}
		return wb;
    }
    
    @Override
    public Integer importScore(User user, MultipartFile file)
    	throws java.io.IOException{
		Workbook wb = null;
		Sheet sheet = null;
		Row captionRow = null;
		
		int count = 0;
		String fileNameLower = file.getOriginalFilename().toLowerCase();
		
		//examName-->examNo
		Map<String,Integer> mapExam = new HashMap<>();

		if (fileNameLower.endsWith(".xlsx"))
			wb = new XSSFWorkbook(file.getInputStream());
		else
			wb = new HSSFWorkbook(file.getInputStream());

		String stuNo = null, stuName=null;
		int rownum;
		try {
			sheet = wb.getSheetAt(0);
			int lastRowNum = sheet.getLastRowNum();
			if (lastRowNum < 1)
				return 0;

			captionRow = sheet.getRow(0);// 文件格式校验

			List<String> lsCaption = new ArrayList<>();
			for (int i = 0; i < captionRow.getLastCellNum(); i++) {
				String szCaption = captionRow.getCell(i).getStringCellValue();
				if(StringUtils.isEmpty(szCaption))
					break;
				lsCaption.add(szCaption);
			}
			if (!lsCaption.get(0).equals("学号") || lsCaption.size() <3) {
				throw new BizException(GlobalErrorCode.INVALID_ARGUMENT,
						"文件格式错误  标题错误");
			}
			//第三列起
			for(int i=2; i < lsCaption.size(); i++){
				String examName = lsCaption.get(i);
				Exam exam = examService.loadByName(examName);
				if(exam == null)
					throw new BizException(GlobalErrorCode.INVALID_ARGUMENT,
							"考试不存在-" + examName);
				mapExam.put(examName, exam.getNo());
			}

			for (rownum = 1; rownum <= lastRowNum; rownum ++) {
				BigDecimal score = null;
				
				Row row = sheet.getRow(rownum);
				
				Cell cell = null;
				cell = row.getCell(0);
				stuNo = getCellValue(cell);
				
				cell = row.getCell(1);
				stuName = cell.getStringCellValue();
				
				
				Student stu = null;
				if("NAME".equalsIgnoreCase(getUniqueFld()) ){
					if(StringUtils.isEmpty(stuName))
						continue;
					List<Student> lsStu = studentMapper.selectByStuName(stuName);
					if(lsStu.size() ==1)
						stu = lsStu.get(0);
				}else{
					if(StringUtils.isEmpty(stuNo))
						continue;
					stu = studentMapper.selectByStuNo(stuNo);
				}
				if(stu == null){
					getLogger().info("学生没找到 stuNo=" +stuNo + " name=" + stuName);
					continue;
				}
				
				for(int cellnum=2; cellnum <lsCaption.size(); cellnum ++){
					cell = row.getCell(cellnum);
					
					if(cell != null){
						if(cell.getCellTypeEnum() == CellType.STRING)
							score = new BigDecimal(cell.getStringCellValue());
						else
							score = new BigDecimal(cell.getNumericCellValue());
					
						score = score.add( new BigDecimal(0.000001));
						score = score.setScale(1, BigDecimal.ROUND_HALF_UP);
					}else{
						score = null;
					}
					
					Integer examNo = mapExam.get(lsCaption.get(cellnum));
					this.updateScore(stu.getId(), examNo, score);
				}
			}
		} catch (BizException e) {
			throw e;
		} catch (Exception e) {
			throw new BizException(GlobalErrorCode.INTERNAL_ERROR, "导入失败");
		} finally {
			if (wb != null)
				wb.close();
		}

		return count;
    }
    
    @Override
    public Integer importStudents(User user, MultipartFile file)
    	throws java.io.IOException{
		Workbook wb = null;
		Sheet sheet = null;
		
		int count = 0;

		String fileNameLower = file.getOriginalFilename().toLowerCase();

		if (fileNameLower.endsWith(".xlsx"))
			wb = new XSSFWorkbook(file.getInputStream());
		else
			wb = new HSSFWorkbook(file.getInputStream());

		Student stu = null;
		int rownum = 0;
		try {
			sheet = wb.getSheetAt(0);
			int lastRowNum = sheet.getLastRowNum();
			if (lastRowNum < 1)
				return 0;
			
			StuParent stuParent = null;
			Row row = null;
			Cell cell = null;
			for (rownum = 1; rownum <= lastRowNum; rownum++) {
				row = sheet.getRow(rownum);
				
				cell = row.getCell(1);
				String stuName = cell.getStringCellValue();
				if(StringUtils.isNotEmpty(stuName)){
					if(stu !=null )
						this.save(user, stu);
										
					
					//学号
					String stuNo = getCellValue(row.getCell(0));
					stu = studentMapper.selectByStuNo(stuNo);
					if(stu == null){
						stu = new Student();
						stu.setStuNo(stuNo);
					}
					stu.setStuName(stuName);
					
					//性别
					String gender = row.getCell(2).getStringCellValue();
					gender = (gender == null?null:gender.trim());
					if("男".equals(gender) || "女".equals(gender) )
						stu.setGender(gender);
					
					stu.setSchool( getCellValue(row.getCell(3)) );
					
					if(row.getLastCellNum() >8)
						stu.setGrade( getCellValue(row.getCell(8)) );
					if(row.getLastCellNum() >9)
						stu.setTimeName( getCellValue(row.getCell(9)) );
					if(row.getLastCellNum() >10)
						stu.setFee( getCellValue(row.getCell(10)) );
				}
				
				//联系人信息
				stuParent = new StuParent();
				stu.addStuParent(stuParent);
				
				stuParent.setRelation( getCellValue(row.getCell(4)) );
				stuParent.setName( getCellValue(row.getCell(5)) );				
				stuParent.setDanwei( getCellValue(row.getCell(6)) );
				
				String szMobile = null;
				cell = row.getCell(7);
				if(cell.getCellTypeEnum() == CellType.NUMERIC){
					double d = cell.getNumericCellValue() + 0.001;
					BigDecimal big = new BigDecimal(d);
					szMobile = String.valueOf( big.longValue() );
				}else{
					szMobile = cell.getStringCellValue();
				}
				stuParent.setMobile( szMobile );
			}
			if(stu !=null )
				this.save(user, stu);
		} catch (Exception e) {
			getLogger().error("rownum=" + rownum + " stuNo=" + stu.getStuNo() + " stuName=" + stu.getStuName(), e);
			throw new BizException(GlobalErrorCode.INTERNAL_ERROR, "导入失败");
		} finally {
			if (wb != null)
				wb.close();
		}

		return count;
    }

    @Override
    public List<Student> queryStudent(StudentSearchForm form, Pageable pager){
    	List<Student> ls = studentMapper.queryStudent(form, pager);
    	if(ls == null)
    		return new ArrayList<>();

    	for(Student stu:ls){
    		Long stuId = stu.getId();
    		List<StuParent> stuParents = stuParentMapper.selectByStuId(stuId);
    		if(stuParents == null)
    			stuParents = new ArrayList<>();
    		
    		stu.setStuParents(stuParents);
    		/*
    		StuScore stuScore = stuScoreMapper.selectByStuId(stuId);
    		if(stuScore == null){
    			stuScore = new StuScore();
    		}
    		stu.setStuScore(stuScore);
    		*/
    	}
    	
    	
    	return ls;
    }
    
    @Override
    public int countStudent(StudentSearchForm form){
    	Integer count = studentMapper.countStudent(form);
    	
    	return count == null?0:count.intValue();
    }
    
    
    private String createStuNo_1(){
    	java.util.Calendar cal = java.util.Calendar.getInstance();
    	int iYear = cal.get( java.util.Calendar.YEAR );
    	String idCode = "STU_NO";
		Maxid maxid = maxidMapper.select(idCode, iYear);
		if(maxid == null){
			maxid = new Maxid();
			maxid.setIdCode(idCode);
			maxid.setDay(iYear);
			maxid.setMaxid(1L);
			maxidMapper.insert(maxid);
		}else{
			maxid.setMaxid(maxid.getMaxid() +1L);
			maxidMapper.update(maxid);
		}
		java.text.DecimalFormat df = new java.text.DecimalFormat("0000");
		return iYear + df.format(maxid.getMaxid());
	}
    
    private String getCellValue(Cell cell){
    	if(cell == null)
    		return null;
    	if(cell.getCellTypeEnum() == CellType.STRING)
    		return cell.getStringCellValue();
    	
    	if(cell.getCellTypeEnum() == CellType.NUMERIC){
			double d = cell.getNumericCellValue() + 0.001;
			BigDecimal big = new BigDecimal(d);
			return String.valueOf( big.longValue() );
		}
    	return null;
    }

}
